package cn.edu.cqupt.cxcy.util;

import java.io.InputStream;
import java.util.List;
import java.util.Properties;

import java.sql.*;

public class Conn {

	/**
	 * 获取数据库连接
	 * @return
	 */
	public static Connection getConnect() {
		Connection conn = null;
		try {
			InputStream is = null;
			is = Conn.class.getClassLoader().getResourceAsStream("jdbcMysql.properties");
			Properties p = new Properties();
			p.load(is);
			String Driver = p.getProperty("Driver");
			String url = p.getProperty("url");
			String username = p.getProperty("username");
			String password = p.getProperty("password");
			Class.forName(Driver);
			conn = DriverManager.getConnection(url, username, password);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return conn;
	}

	/**
	 * 更新操作
	 * 
	 * @param sql 更新语句
	 * @param parameters 数组方式传递参数(参数一定不能null)
	 * 
	 * <p>用法说明:</p>
	 * String sql = "update orders set platenumber=?,variety=?,eachprice=? where id=11";<br />	
	 *		try {<br />	
	 *			ps = con.prepareStatement(sql);<br />	
	 *		ps.setString(1, order.getPlatenumber());<br />	
	 *		ps.setString(2, order.getVariety());<br />	
	 *		ps.setDouble(3, order.getEachprice());<br />	
	 *	<br />	
	 * 		ps.executeUpdate();<br />	
	 * 		} catch<br />	
	 */
	public static boolean executeUpdate(String sql, List<Object> parameters) {
		int ii = 0;
		Connection conn = null;
		PreparedStatement ps = null;
		try {
			conn = getConnect();
			ps = conn.prepareStatement(sql);
			if (parameters != null)
				for (int i = 0; i < parameters.size(); i++) {
					//此处可能有异常
					String type = parameters.get(i).getClass().getSimpleName();
					if(type.equalsIgnoreCase("String")) {
						ps.setString(i + 1, (String) parameters.get(i));
					} else if (type.equalsIgnoreCase("Double")) {
						ps.setDouble(i + 1, (double) parameters.get(i));
					} else if (type.equalsIgnoreCase("Integer")) {
						ps.setInt(i + 1, (int) parameters.get(i));
					} else if (type.equalsIgnoreCase("Float")) {
						ps.setFloat(i + 1, (float) parameters.get(i));
					} else if (type.equalsIgnoreCase("Boolean")) {
						ps.setBoolean(i + 1, (boolean) parameters.get(i));
					}
				}
			ii = ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
			throw new RuntimeException(e.getMessage());
		} finally {
			close(null, ps, conn);
		}
		return ii==0?false:true;
	}

	/**
	 * 查询方法
	 * 
	 * @param sql 查询语句
	 * @param parameters 传递参数(参数一定不能null)
	 * 
	 * <p>对结果集的取值的说明</p>
	 * while (rs.next()) { <br />
	 *	order.setOrderid(rs.getInt("orderid"));<br />
	 *	order.setPlatenumber(rs.getString("platenumber"));<br />
	 *	order.setVariety(rs.getString("variety"));<br />
	 *	order.setEachprice(rs.getDouble("eachprice"));<br />
	 * } <br />
	 * @return 用model里面的实体类装
	 */
	public static ResultSet executeSelect(String sql, List<Object> parameters) {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			conn = getConnect();
			ps = conn.prepareStatement(sql);
			if (parameters != null) {
				for (int i = 0; i < parameters.size(); i++) {
					//此处可能有异常
					String type = parameters.get(i).getClass().getSimpleName();
					if(type.equalsIgnoreCase("String")) {
						ps.setString(i + 1, (String) parameters.get(i));
					} else if (type.equalsIgnoreCase("Double")) {
						ps.setDouble(i + 1, (double) parameters.get(i));
					} else if (type.equalsIgnoreCase("Integer")) {
						ps.setInt(i + 1, (int) parameters.get(i));
					} else if (type.equalsIgnoreCase("Float")) {
						ps.setFloat(i + 1, (float) parameters.get(i));
					} else if (type.equalsIgnoreCase("Boolean")) {
						ps.setBoolean(i + 1, (boolean) parameters.get(i));
					}
				}
			}
			rs = ps.executeQuery();
		} catch (SQLException e) {
			e.printStackTrace();
			throw new RuntimeException(e.getMessage());
		} finally {
			close(null, ps, conn);
		}
		return rs;
	}
	
	/**
	 * 删除操作
	 * 
	 * @param sql 语句
	 * @return 
	 */
	public static boolean executeDelete(String sql) {
		int i = 0;
		Connection conn = null;
		PreparedStatement ps = null;
		try {
			conn = getConnect();
			ps = conn.prepareStatement(sql);
			i = ps.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			close(null, ps, conn);
		}
		return i==0?false:true;
	}

	/**
	 * 插入操作
	 * 
	 * @param sql 插入语句
	 * @param parameters 传入参数(参数一定不能null)
	 * @return 由于本数据id全为int类型，因此只返回int(FAIL:0; SUCCESS: >0)<br />
	 * 返回插入数据的ID
	 */
	public static int executeInsert(String sql, List<Object> parameters) {
		int ii = 0;
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			conn = getConnect();
			ps =  conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
			if (parameters != null) {
				for (int i = 0; i < parameters.size(); i++) {
					//此处可能有异常
					String type = parameters.get(i).getClass().getSimpleName();
					if(type.equalsIgnoreCase("String")) {
						ps.setString(i + 1, (String) parameters.get(i));
					} else if (type.equalsIgnoreCase("Double")) {
						ps.setDouble(i + 1, (double) parameters.get(i));
					} else if (type.equalsIgnoreCase("Integer")) {
						ps.setInt(i + 1, (int) parameters.get(i));
					} else if (type.equalsIgnoreCase("Float")) {
						ps.setFloat(i + 1, (float) parameters.get(i));
					} else if (type.equalsIgnoreCase("Boolean")) {
						ps.setBoolean(i + 1, (boolean) parameters.get(i));
					}
				}
			}
			ps.executeUpdate();
			rs = ps.getGeneratedKeys();  
			while (rs.next()) {
				ii = rs.getInt(1);
			}
		} catch (Exception e1) {
			e1.printStackTrace();
			throw new RuntimeException(e1.getMessage());
		} finally {
			close(rs, ps, conn);
		}
		return ii;
	}

	/**
	 * 关流操作
	 * 
	 * @param rs 没有此参数时为null
	 * @param ps 没有此参数时为null
	 * @param conn 没有此参数时为null
	 */
	public static void close(ResultSet rs, Statement ps, Connection conn) {
		if (rs != null)
			try {
				rs.close();
				rs = null;
			} catch (SQLException e) {
				e.printStackTrace();
			}
		if (ps != null)
			try {
				ps.close();
				ps = null;
			} catch (SQLException e) {
				e.printStackTrace();
			}
		if (conn != null)
			try {
				conn.close();
				conn = null;
			} catch (SQLException e) {
				e.printStackTrace();
			}
	}
}

